package com.insleton.admin.employee.service.impl;

import com.insleton.admin.employee.dao.DepartmentDao;
import com.insleton.admin.employee.dao.EmployeeDao;
import com.insleton.admin.employee.dao.EmployeeJpaDao;
import com.insleton.admin.employee.model.BatchTest;
import com.insleton.admin.employee.model.Department;
import com.insleton.admin.employee.model.EmpDTO;
import com.insleton.admin.employee.model.EmpVo;
import com.insleton.admin.employee.model.Employee;
import com.insleton.admin.employee.model.QEmployee;
import com.insleton.admin.employee.model.SubEmp;
import com.insleton.admin.employee.service.CriteriaQueryCallback;
import com.insleton.admin.employee.service.EmployeeService;
import com.insleton.admin.employee.sql.DepartmentSQL;
import com.querydsl.core.Tuple;
import com.querydsl.jpa.impl.JPAQueryFactory;
import com.querydsl.sql.mysql.MySQLQueryFactory;
import com.vteba.tx.dao.Page;
import com.vteba.tx.hibernate.transformer.SQLResultTransformer;
import com.vteba.utils.id.ObjectId;
import com.vteba.utils.ofbiz.Utils;
import com.vteba.utils.reflection.BeanUtils;
import jakarta.inject.Inject;
import jakarta.inject.Named;
import jakarta.persistence.EntityManager;
import jakarta.persistence.TypedQuery;
import jakarta.persistence.criteria.Root;
import jakarta.transaction.Transactional;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.hibernate.Session;
import org.hibernate.query.NativeQuery;
import org.hibernate.query.criteria.HibernateCriteriaBuilder;
import org.hibernate.query.criteria.JpaCriteriaQuery;
import org.hibernate.query.criteria.JpaPredicate;
import org.springframework.data.domain.Example;
import org.springframework.data.domain.ExampleMatcher;

import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Optional;

@Named
@Transactional
public class EmployeeServiceImpl extends GenericServiceImpl<Employee, String> implements EmployeeService {

    private static final Logger LOGGER = LogManager.getLogger(EmployeeServiceImpl.class);

    protected Session getSession() {
        return entityManager.unwrap(Session.class);
    }

    @Inject
    private EmployeeDao employeeDao;
    // 这两个属性，需要的话可以注入进来使用
    @Inject
    private EntityManager entityManager;
    // @Inject
    // private SessionFactory sessionFactory;
    @Inject
    private EmployeeJpaDao employeeJpaDao;
    @Inject
    private MySQLQueryFactory mySQLQueryFactory;
    @Inject
    private JPAQueryFactory jpaQueryFactory;
    @Inject
    private DepartmentDao departmentDao;

    public void queryTest(Employee eee) {
        String sql = "select * from employee e where e.name like ?1";
        List<Object[]> objectList = employeeJpaDao.sqlQueryObject(sql, "尹%");

        Employee countQuery = new Employee();
        countQuery.setName("尹雷");
        Long l = employeeJpaDao.countSQL(countQuery);

        List<Employee> employeeList = employeeJpaDao.queryListSQL(sql, "尹%");
        List<Employee> employeeList2 = employeeJpaDao.queryListSQL(sql, Employee.class, "尹%");

        List<Employee> employeeList5 = employeeJpaDao.queryListNamedSQL("Employee.SQL.Entity");
        String empId = employeeList5.get(0).getId();
        List<Employee> employeeList52 = employeeJpaDao.queryListNamedSQL("Employee.SQL.Entity.param", empId);
        // 命名SQL也可以查询部分字段
        List<Employee> employeeList53 = employeeJpaDao.queryListNamedSQL("Employee.SQL.Entity.no.alias");

        LOGGER.info("queryTest 查询结束");
    }

    @Transactional
    public void test() {
       Department query = new Department();
       query.setId("62332c5ec00ffd71b50ef20d");

       Map<String, String> orderMap = new HashMap<>();
       orderMap.put("name", "desc");
       // TODO 1、POJO查询当前类list
       List<Department> departments = departmentDao.queryList(query);
       List<Department> departments2 = departmentDao.queryList(query, orderMap);

       Map<String, String> params = new HashMap<>();
       params.put("name", "研发部");
       // TODO 2、map查询当前类list
       List<Department> departments3 = departmentDao.queryList(params);
       List<Department> departments4 = departmentDao.queryList(params, orderMap);
       // SQL查询当前类，这里是使用了转换器？一定要查询所有的字段吗?当前类，好像必须要查询所有字段。那么也就不需要转换器
       // 不是要查询所有字段，只要create query，不使用class就行了。使用转换器
       // TODO 3、SQL查询当前类
       List<Department> departmentList = departmentDao.queryListSQL(DepartmentSQL.EMP_DEPT_SQL_ALL);
       // 查询当前类，createQuery的时候，不指定class就好了，使用我们自己的transformer
       List<Department> departmentList4 = departmentDao.queryListSQL(DepartmentSQL.EMP_DEPT_SQL_PARAM, "研发部");
       // TODO 4、SQL查询扩展VO类
       // SQL查询，使用的是转换器。只要第二个参数给了class，基本上就是使用转换器
       List<EmpVo> empVoList = departmentDao.queryListSQL(DepartmentSQL.EMP_DEPT_SQL, EmpVo.class);
       // 转换器里面使用SQL作为缓存键，同样的sql查询返回不同的类，如果类中字段不对应，将会导致字段不匹配错误。上上一行，返回Department，这里返回EmpVo
       List<EmpVo> empVoList_pa = departmentDao.queryListSQL(DepartmentSQL.EMP_DEPT_SQL_PARAM, EmpVo.class, "研发部");
       List<EmpVo> empVoList_pB = departmentDao.queryListSQL(DepartmentSQL.EMP_DEPT_SQL_NAMED_PARAM, EmpVo.class, params);
       // TODO 5、HQL查询扩展VO类
       // HQL查询，使用的转换器
       List<EmpVo> empVoList2 = departmentDao.queryListHQL(DepartmentSQL.EMP_DEPT_HQL, EmpVo.class);
       List<EmpVo> empVoList2_pa = departmentDao.queryListHQL(DepartmentSQL.EMP_DEPT_HQL_PARAM, EmpVo.class, "研发部");
       List<EmpVo> empVoList2_pB = departmentDao.queryListHQL(DepartmentSQL.EMP_DEPT_HQL_NAMED_PARAM, EmpVo.class, params);
       // TODO 6、hql查询当前类
       // 下面4个，使用的是构造函数
       List<Department> departmentList1 = departmentDao.queryListHQL(DepartmentSQL.EMP_DEPT_HQL);
       List<Department> departmentList0 = departmentDao.queryListHQL(DepartmentSQL.EMP_DEPT_HQL_PARAM, "研发部");
       List<Department> departmentList2 = departmentDao.queryListHQL(DepartmentSQL.EMP_DEPT_HQL_NAMED_PARAM, params);
       Department deptQuery = new Department();
       deptQuery.setName("研发部");
       List<Department> departmentList3 = departmentDao.queryListHQL(DepartmentSQL.EMP_DEPT_HQL_NAMED_PARAM, deptQuery);
       // TODO 7、命名HQL查询当前类
       // hql查询，这里是hibernate直接转换的
       List<Department> list = departmentDao.queryListNamedHQL("Department.selectAll");
       List<Department> list_p1 = departmentDao.queryListNamedHQL("Department.selectAll.param", "研发部");
       List<Department> list_p3 = departmentDao.queryListNamedHQL("Department.selectAll.named.param", deptQuery);
       List<Department> list_p2 = departmentDao.queryListNamedHQL("Department.selectAll.named.param", params);
       // hql查询当前类，两种方法：1是有对应的构造函数，可以不在hql语句中使用。2是使用转换器
       // 这四个使用了构造函数
       List<Department> list2 = departmentDao.queryListNamedHQL("Department.queryLink");
       List<Department> list2_2 = departmentDao.queryListNamedHQL("Department.queryLink.param", "研发部");
       List<Department> list2_3 = departmentDao.queryListNamedHQL("Department.queryLink.named.param", params);
       List<Department> list2_4 = departmentDao.queryListNamedHQL("Department.queryLink.named.param", deptQuery);
       // TODO 8、命名hql查询扩展VO类
       // 使用了转换函数
       List<EmpVo> list3_2 = departmentDao.queryListNamedHQL("Department.queryLink.param", EmpVo.class, "研发部");
       List<EmpVo> list3_3 = departmentDao.queryListNamedHQL("Department.queryLink.named.param", EmpVo.class, params);
       List<EmpVo> list3_4 = departmentDao.queryListNamedHQL("Department.queryLink.named.param", EmpVo.class, deptQuery);

       // hql中直接使用构造函数来转换
       List<Department> list3 = departmentDao.queryListNamedHQL("Department.queryLinkConstructor");


       Map<String, String> paramsMap = new HashMap<>();
       paramsMap.put("name", "尹大锤");
       List<Employee> employeeList6 = employeeJpaDao.queryListNamedHQL("Employee.HQL.sub.query", paramsMap);

       List<Employee> employeeList7 = employeeJpaDao.queryListNamedHQL("Employee.HQL.sub.index", "尹大锤");
       // TODO 9、命名SQL查询当前类
       List<Employee> employeeList5 = employeeJpaDao.queryListNamedSQL("Employee.SQL.Entity");
       String empId = employeeList5.get(0).getId();
       List<Employee> employeeList52 = employeeJpaDao.queryListNamedSQL("Employee.SQL.Entity.param", empId);
       Map<String, Object> map = Utils.toMap("id", empId);
       List<Employee> employeeList53 = employeeJpaDao.queryListNamedSQL("Employee.SQL.Entity.named", map);

       // TODO 10、命名SQL查询扩展VO类
       List<EmpVo> employeeList71 = employeeJpaDao.queryListNamedSQL("Employee.SQL.dto.index", EmpVo.class, empId); // jpa顺序参数

       // sql不要使用命名参数了，使用问号占位符，就可以了。使用索引绑定
       List<EmpVo> employeeList72 = employeeJpaDao.queryListNamedSQL("Employee.SQL.dto", EmpVo.class, map); // 命名参数
       EmpVo empVo = new EmpVo();
       empVo.setId(empId);
       List<EmpVo> employeeList73 = employeeJpaDao.queryListNamedSQL("Employee.SQL.dto", EmpVo.class, empVo); // Bean 命名参数

       // TODO 11、sql/hql查询基本类型数据，一般用于统计和聚合函数
       String sql = "select count(*) count from employee";
       long s = System.currentTimeMillis();
       Integer count1 = departmentDao.sqlQueryPrimitive(sql, Integer.class);
       System.out.println(System.currentTimeMillis() - s);

       String hql1 = "select count(*) count from Employee";
       s = System.currentTimeMillis();
       Integer count2 = departmentDao.hqlQueryPrimitive(hql1, Integer.class);
       System.out.println(System.currentTimeMillis() - s);

       // TODO 12、sql/hql 查询任意结果，返回Object[]，可自由转换
       String sql2 = "select * from employee";
       // 这种查询除非是没有定义VO，否则是没有必要的，可以使用其他的list方法
       s = System.currentTimeMillis();
       List<Object[]> objectList = departmentDao.sqlQueryObject(sql2);
       System.out.println(System.currentTimeMillis() - s);

       String hql2 = "select u from Employee u";
       s = System.currentTimeMillis();
       List<Object[]> objects = departmentDao.hqlQueryObject(hql2, false);
       System.out.println(System.currentTimeMillis() - s);

       // TODO 13、分页类查询
       Page<Department> page = new Page<>();

       Department dept = new Department();
       dept.setName("研发部");
       List<Department> departmentList5 = departmentDao.pagedQueryList(DepartmentSQL.DEPT_HQL_PAGE, dept);
       List<Department> departmentList51 = departmentDao.pagedQueryList(page, dept);
       Page<Department> page1 = departmentDao.queryForPage(page, dept);

       Map<String, Object> deptMap = new HashMap<>();
       deptMap.put("name", "研发部");
       List<Department> departmentList52 = departmentDao.pagedQueryList(DepartmentSQL.DEPT_HQL_PAGE, deptMap);
       List<Department> departmentList53 = departmentDao.pagedQueryList(page, deptMap);
       Page<Department> page2 = departmentDao.queryForPage(page, deptMap);

       Long l = departmentDao.countSQL(DepartmentSQL.DEPT_SQL_PAGE, deptMap);
       Long l2 = departmentDao.countSQL(DepartmentSQL.DEPT_SQL_PAGE, dept);

       // TODO 14、唯一查询
       dept.setName("董事会");
       Department department = departmentDao.uniqueResult(dept);
       deptMap.put("name", "董事会");
       Department department2 = departmentDao.uniqueResult(deptMap);

       Map<String, Object> idMap = new HashMap<>();
       idMap.put("id", "62332c91c00ffd71b50ef20f");
       Department d1 = departmentDao.uniqueResultSQL(DepartmentSQL.DEPT_SQL_UNIQUE, idMap);
       Department d2 = departmentDao.uniqueResultSQL(DepartmentSQL.DEPT_SQL_UNIQUE_INDEX, "62332c91c00ffd71b50ef20f");
       Department d3 = departmentDao.uniqueResultHQL(DepartmentSQL.DEPT_HQL_UNIQUE, deptMap);
       Department d4 = departmentDao.uniqueResultHQL(DepartmentSQL.DEPT_HQL_UNIQUE_INDEX, "董事会");

       Department d5 = departmentDao.uniqueResultNamedHQL("Department.selectAll.named.param", deptMap);
       Department d6 = departmentDao.uniqueResultNamedHQL("Department.selectAll.param", "董事会");

       Map<String, Object> empMap = new HashMap<>();
       empMap.put("name", "尹雷");
       Employee d7 = departmentDao.uniqueResultBySQL(DepartmentSQL.EMP_SQL_UNIQUE_INDEX, Employee.class, "尹雷");
       Employee d8 = departmentDao.uniqueResultBySQL(DepartmentSQL.EMP_SQL_UNIQUE_NAMED, Employee.class, empMap);
       // TODO 15、唯一查询扩展
       Employee d9 = departmentDao.uniqueResultByHQL(DepartmentSQL.EMP_HQL_UNIQUE_INDEX, Employee.class, "尹雷");
       Employee d10 = departmentDao.uniqueResultByHQL(DepartmentSQL.EMP_HQL_UNIQUE_NAMED, Employee.class, empMap);
       Employee d11 = departmentDao.uniqueResultByHQL(DepartmentSQL.EMP_HQL_UNIQUE_INDEX_FIELD, Employee.class, "尹雷");
       Employee d12 = employeeJpaDao.uniqueResultByHQL(DepartmentSQL.EMP_HQL_UNIQUE_INDEX_FIELD, Employee.class, "尹雷");

       Employee save1 = new Employee();
       save1.setId(ObjectId.get().toString());
       save1.setName("尹千亿");
       save1.setEntryTime(new Date());
       save1.setEmail("128888@128.com");
       save1.setState(1);
       save1.setAccount("likl");
       save1.setDeptId("62332c91c00ffd71b50ef20f");
       String idd = employeeJpaDao.save(save1);

       Employee save11 = new Employee();
       save11.setId(ObjectId.get().toString());
       save11.setName("尹百亿");
       save11.setEntryTime(new Date());
       save11.setDeptId("62332c91c00ffd71b50ef20f");
       save11.setState(1);
       employeeJpaDao.persist(save11);

       String id = ObjectId.get().toString();
       Employee save12 = new Employee();
       save12.setId(id);
       save12.setName("王一牛");
       save12.setEntryTime(new Date());
       // merge 的确可以实现保存的功能。主要用于更新
       /**
        * 是的，JPA的merge方法可以实现保存功能，但它的行为更加复杂和灵活，因为它旨在处理实体的合并。具体来说，merge方法会根据实体的状态（即其是否被持久化上下文管理）来决定是创建一个新的持久化实例、更新一个已存在的实例，还是忽略实体的更改。
        *
        * 这里简要说明一下merge方法在不同情况下的行为：
        *
        * 如果实体是新的（即未被持久化上下文管理）：JPA将复制该实体的状态到一个新的、被持久化上下文管理的实体实例中，并返回这个新实例。然后，你可以认为这个新实例被“保存”到了数据库中。实际上，merge在这里间接实现了保存功能，因为它通过创建一个新的持久化实例来反映实体的当前状态，并将这个状态持久化到数据库中。
        * 如果实体是已存在的（即已被持久化上下文管理）：在这种情况下，JPA会检查传入实体的状态与当前持久化上下文中管理的实体的状态是否不同。如果不同，它会将传入实体的状态合并到当前持久化上下文管理的实体中。这意味着，如果有任何更改，这些更改会被更新到数据库（当然，这取决于事务的提交）。如果没有更改，则不执行任何数据库操作。
        * 如果实体已经存在于数据库中，但不在当前持久化上下文中：merge方法会根据实体的主键来查找数据库中是否存在具有相同主键的实体。如果存在，它将合并这两个实体的状态（以传入实体的状态为准），并将更新后的状态保存到数据库中。如果数据库中没有具有相同主键的实体，JPA将创建一个新的实体实例，并将其状态保存到数据库中。
        * 总的来说，虽然merge方法的主要用途是合并实体的状态，但它确实可以用来实现保存功能，尤其是在处理新实体或需要从数据库同步状态的情况下。然而，如果你只是想要保存一个新实体，而不需要处理任何复杂的合并逻辑，使用persist方法可能会更直接和清晰。同样，如果你想要更新一个已存在的实体，而该实体已经在持久化上下文中，直接修改其状态并在事务提交时由JPA自动更新到数据库通常会更简单。
        */
       // 新增 王一牛
       Employee merge1 = employeeJpaDao.merge(save12);
       // 这里没有找到，但是不保存，是因为本来就是获取引用的
       Employee emp2 = employeeJpaDao.getReference(id);
       Employee emp2_1 = employeeJpaDao.getReference(idd);
       // 这里没有查询到是因为使用的是merge保存的，还在当前事务上下文，还没有真正的保存。
       // Employee emp3 = employeeJpaDao.get(id);
       Employee emp3 = employeeJpaDao.get(idd);

       // update的实现，就是merge
       merge1.setName("王二牛"); // 将王一牛，改为王二牛
       employeeJpaDao.updateById(merge1);

       Employee emp4 = new Employee();
       // emp4.setId(id);
       // 尹千亿 改为 王三牛
       emp4.setId(idd); // 是因为这个id，上面是使用的merge，不是真的保存。将id改为idd就应该不会出错了
       emp4.setName("王三牛");
       emp4.setEntryTime(new Date());
       // 这种才是更新的最好的方式，不会更新不要的字段（貌似还是更新了所有的字段，还是使用hql才能不更新所有的字段）
       // 这里merge错误，Unable to find com.insleton.admin.employee.model.Employee with id 66e3e280a58b1f7cedd65303
       // 是因为这个id，上面是使用的merge，不是真的保存。将id改为idd就应该不会出错了
       employeeJpaDao.merge(emp4); // 尹千亿 改为 王三牛

       String id13 = ObjectId.get().toString();
       // hibernate都是按照引用判断是不是同一个对象（脏检查）。如果引用相同，但是id不同。这个时候，hibernate merge会报错。还是重新new 一个对象比较方便
       Employee save13 = new Employee();
       save13.setId(id13);
       save13.setName("尹2牛");
       save13.setEntryTime(new Date());
       save13.setState(2);
       save13.setDeptId("62332c91c00ffd71b50ef20f");
       employeeJpaDao.persist(save13);

       Employee emp5 = employeeJpaDao.getReference(Employee.class, id13);
       // Unable to find com.insleton.admin.employee.model.Employee with id 66e3e3716e4f3b11875d1d0f
       // 事务还没有提交，所以找不到id2
       // 直接修改id，保存，好像出错了
       // identifier of an instance of com.insleton.admin.employee.model.Employee was altered from 66e3e5cddf432d5a893cf35e to 66e3e5cddf432d5a893cf35b
       // employeeJpaDao.flush(); // 强制刷新事务
       employeeJpaDao.deleteById(id13); // 按照id删除有问题？

       Employee save2 = new Employee();
       save2.setId(ObjectId.get().toString());
       save2.setName("尹3牛");
       save2.setEntryTime(new Date());
       save2.setState(2);
       save2.setDeptId("62332c91c00ffd71b50ef20f");
       String id3 = employeeJpaDao.save(save2);

       Employee emp6 = employeeJpaDao.get(id3);
       // employeeJpaDao.delete(emp6); // OK的
       // employeeJpaDao.delete(id3); // OK的

       // 3个数据，尹百亿，王二牛，王三牛，尹2牛

       Employee delete = new Employee();
       delete.setId(id3);

       int di = employeeJpaDao.deleteBatch(delete);

       String deleteSQL1 = "delete from employee where name = ?1";
       String deleteSQL2 = "delete from employee where name = :name";
       int ds3 = employeeJpaDao.updateBatchSQL(deleteSQL1, "尹百亿");

       Map<String, Object> deleteMap = new HashMap<>();
       // deleteMap.put("id", "1233"); // 对于命名参数，多/少传参数是会报错的。不管SQL还是HQL，都会保存，在设置Query参数的时候就报错了。位置参数是一样的
       deleteMap.put("name", "尹百亿");
       int ds5 = employeeJpaDao.updateBatchSQL(deleteSQL2, deleteMap);
       // 如果使用model传参，model需要实现 AstModel接口
       Employee deleteEmp = new Employee();
       deleteEmp.setName("尹百亿");
       int ds4 = employeeJpaDao.updateBatchSQL(deleteSQL2, deleteEmp);

       System.out.println(di);
   }

   @Transactional
   public void test2() {
       QEmployee employee = QEmployee.employee;
       List<Tuple> empList = mySQLQueryFactory.from(employee).select(employee.id, employee.name).fetch();
       // List<?> empList3 = mySQLQueryFactory.from(employee).fetch();

       // List<?> empList2 = jpaQueryFactory.from(employee).fetch();

       // Session session = sessionFactory.getCurrentSession();
       Session session = getSession(); // 这个才是获取底层session的正确方式。否则事务开启会出现错误
       NativeQuery nativeQuery = (NativeQuery) session.createNamedQuery("Employee.SQL.dto.h");
       // createNamedQuery,不指定class参数，就可以使用转换器，查询任意字段，返回任意VO类
       // NativeQuery nativeQuery = (NativeQuery) session.createNamedQuery("Employee.SQL.dto.h", EmpDTO.class);
       nativeQuery.setParameter("id", "667a7e52b0d23605aa924264");
       // 不设置转换器，（不是转换器的作用，其实是创建query的时候，不指定class参数）会返回object[]
       String nativeSQL = nativeQuery.getQueryString();
       SQLResultTransformer transformer = new SQLResultTransformer(EmpDTO.class, nativeSQL);
       // Class<?>[][] argsTypes = transformer.getArgsTypes();
       // String[] columnAlias = transformer.getColumnAlias();
       // 1、不需要设置标量
       // for (int j = 0; j < columnAlias.length; j++) {
       //     if (columnAlias[j] != null) {
       //         // 这里设置标量，字段应该和SQL一样，否则就会出现找不到column的错误
       //         nativeQuery.addScalar(columnAlias[j], argsTypes[j][0]);
       //     }
       // }
       // 2、也不需要转换了。将POJO标记为@Entity后，就不需要转换了。
       // 使用转换器更方便，可以查询任意字段
       nativeQuery.setTupleTransformer(transformer);
       List list = nativeQuery.list();

       System.out.println();
   }

    @Override
    public void saveBatch(List<BatchTest> batchList) {
        String insertSQL = "insert into batch_test values(?, ?, ?, ?, ?, ?)";

        // 构造数据1
        // List<BatchTest> testList = new ArrayList<>();
        // for (int i = 0; i < 50002; i++) {
        //     BatchTest test = new BatchTest();
        //     test.setId(ObjectId.get().toString());
        //     test.setName(ObjectId.get().toString());
        //     test.setAge(28);
        //     test.setSalary(3456.89D);
        //     test.setCreateDate(new Date());
        //     test.setRemark("尹千亿");
        //
        //     testList.add(test);
        // }

        List<Object[]> testList = new ArrayList<>();
        for (int i = 0; i < 50002; i++) {
            Object[] objects = new Object[6];
            objects[0] = ObjectId.get().toString();
            objects[1] = ObjectId.get().toString();
            objects[2] = 28;
            objects[3] = 3456.89D;
            objects[4] = new Date();
            objects[5] = "尹千亿";

            testList.add(objects);
        }

        long d = System.currentTimeMillis();
        // JdbcTemplate 使用 JavaBean，31秒。因为我内部自己设置了分批。
        // 如果我手动不分批，直接全部数据，也是7秒左右。
        // employeeJpaDao.batchSave(insertSQL, testList);
        // 纯 JDBC 7秒。开启了rewriteBatchedStatements=true，只要1.5秒
        // employeeJpaDao.batchUpdate(testList, insertSQL);
        // JdbcTemplate 使用 Object[] 7秒左右，开启rewriteBatchedStatements=true只要1.6秒
        employeeJpaDao.batchUpdate(insertSQL, testList);

        d = System.currentTimeMillis() - d;
        LOGGER.info("测试结束，耗时=[{}]毫秒", d);
    }

    // @Override
    // public int save(Employee employee) {
    //     Employee saved = employeeDao.save(employee);
    //     return 1;
    // }

   @Transactional
   public List<Employee> list(Employee employee) {
       employee.setState(1);

       String hql1 = " select new Employee(id, name, state) from Employee e ";
//       String hql = " select id id, name name, state state from Employee e "; // 这样查询是OK的。需要构造函数
       String hql = " select id, name, state from Employee e "; // 这样查询也是ok的，不使用别名。需要对应的构造函数
       // 查询的栏位必须和构造函数，一一对应
       String sql = " select id, name, state, account from employee e where id = ?1";
       // 1、构造函数查询
       List<Employee> employeeList1 = employeeJpaDao.queryListHQL(hql1);
       // 2、这种查询具体字段，不使用构造函数也能查询了？不行的，需要有对应的构造函数
       List<Employee> employeeList0 = employeeJpaDao.queryListHQL(hql);
       // 3、OK，使用了转换函数，ResultTransformer（当前Dao对象的实体类，不使用也可以）
       List<Employee> employeeList2 = employeeJpaDao.queryListHQL(hql, Employee.class);
       // 4、OK，命名HQL查询。使用了转换
       List<Employee> employeeList3 = employeeJpaDao.queryListNamedHQL("Employee.HQL");
       // 5、OK，使用命名HQL查询，查询部分字段。使用了转换。非标准hql查询，1是使用转换函数，2是有对应查询字段的构造函数（顺序也要相同）
       // 构造函数，只看参数类型和顺序，不看名称的。所以要注意哦
       List<Employee> employeeList6 = employeeJpaDao.queryListNamedHQL("Employee.HQL.sub");
       Employee uni = new Employee();
       String id = employeeList2.get(0).getId();
       uni.setId(id);
       // OK，构造函数的字段和查询的字段要一一对应
       // 不设置转换函数，默认是这个NativeQueryConstructorTransformer。设置了就按照设置的转换函数来
       List<SubEmp> employeeList4 = employeeJpaDao.queryListSQL(sql, SubEmp.class, id);
       // OK，必须查询所有栏位（是因为创建query的时候，指定了class，不指定就可以查询任意字段了）。现在内部是，先获取命名sql，然后再使用普通的SQL查询的。投机取巧了
       List employeeList5 = employeeJpaDao.queryListNamedSQL("Employee.SQL", SubEmp.class);

       Employee employee1 = employeeJpaDao.uniqueResult(uni);

       Map<String, String> paramsMap = new HashMap<>();
       paramsMap.put("id", id);

       // -----------------
       // named native query，只能查询全部字段。而且result class只能是实体类。将named native query转为native query，参数绑定要基于索引，且从1开始，不能使用命名参数。
       // -----------------
       List employeeList7 = employeeJpaDao.queryListNamedSQL("Employee.SQL.dto.index", EmpVo.class, id); // jpa顺序参数

       // sql不要使用命名参数了，使用问号占位符，就可以了。使用索引绑定
       List employeeList72 = employeeJpaDao.queryListNamedSQL("Employee.SQL.dto", EmpVo.class, paramsMap); // 命名参数
       // TypedQuery<EmpDTO> typedQuery = entityManager.createNamedQuery("Employee.SQL.dto", EmpDTO.class);
       // typedQuery.setParameter("id", id);
       // List list8 = typedQuery.getResultList();

       // 将hibernate和jpa整合使用，已经OK。不能再单独开启事务了 2024-7-15
       // IllegalStateException: Transaction already active
       // Transaction tx = employeeJpaDao.getSession().beginTransaction();
       Employee setValue = new Employee();
       setValue.setEmail("123@456.com");
       setValue.setEntryTime(new Date());
       setValue.setDeptId("123");
       Employee where = new Employee();
       where.setId(id);
       // TransactionRequiredException: Executing an update/delete query
       int k = employeeJpaDao.updateBatch(setValue, where);

       Map<String, Object> whereMap = new HashMap<>();
       whereMap.put("name", "尹大锤");
       int k2 = employeeJpaDao.updateBatch(setValue, whereMap);

       // tx.commit();

       List pageList1 = employeeJpaDao.pagedQueryList(hql + " where id = :id", where);
       List pageList2 = employeeJpaDao.pagedQueryList(hql + " where name = :name", whereMap);

       Employee employee2 = employeeJpaDao.get(id);
       Employee employee3 = employeeJpaDao.getReference(id);
       List employee4 = employeeJpaDao.getAll();

       createCriteriaByModel(employee);

       createCriteria(employee, (hcb, root, params) -> {
           List<JpaPredicate> predicateList = new ArrayList<>();
           for (Map.Entry<String, Object> entry : params.entrySet()) {
               JpaPredicate predicate = hcb.equal(root.get(entry.getKey()), entry.getValue());
               predicateList.add(predicate);
           }

           JpaPredicate[] predicates = new JpaPredicate[predicateList.size()];
           JpaPredicate jpaPredicate = hcb.and(predicateList.toArray(predicates));
           return jpaPredicate;
       });

       List<Employee> employeeList = employeeDao.findAll(Example.of(employee));

       List<Employee> hqlList = employeeDao.queryList("尹雷");
       List<Employee> paramsList = employeeDao.queryBy("尹大锤");

       // Employee emp = new Employee();
       // emp.setName("yin");
       // List<Employee> beanList = employeeDao.queryListByBean(emp);
       //
       // Map<String, Object> map = new HashMap<>();
       // map.put("name", "尹大锤");
       // List<Employee> mapList = employeeDao.queryListByMap(map);

       // return employeeList;
       return Collections.emptyList();
   }

    // public List<Employee> list(Employee employee) {
    //     return Collections.emptyList();
    // }

   protected <X> TypedQuery<X> createCriteriaByModel(X entity) {
       Class<X> clazz = (Class<X>) entity.getClass();
       if (LOGGER.isInfoEnabled()) {
           LOGGER.info("Create Criteria query by QBE. Entity = [{}].", clazz.getName());
       }
       // HibernateCriteriaBuilder hcb = sessionFactory.getCriteriaBuilder();
       HibernateCriteriaBuilder hcb = getSession().getCriteriaBuilder();
       JpaCriteriaQuery<X> jcq = hcb.createQuery(clazz);

       Map<String, Object> map = BeanUtils.toMap(entity);

       // 定义from子句
       Root<X> root = jcq.from(clazz);

       List<JpaPredicate> predicateList = new ArrayList<>();
       for (Map.Entry<String, Object> entry : map.entrySet()) {
           JpaPredicate predicate = hcb.equal(root.get(entry.getKey()), entry.getValue());
           predicateList.add(predicate);
           // jcq.where(predicate); // where 多次调用，会产生多个sql，结果是union all，导致重复数据
       }

       JpaPredicate[] predicates = new JpaPredicate[predicateList.size()];
       // select(root) 也可以不写
       jcq.select(root).where(predicateList.toArray(predicates)); // 多个谓词之间，默认是and关系，如果需要or，需要hcb.or, hcb.and 自己组合

       TypedQuery<X> typedQuery = entityManager.createQuery(jcq);
       // TypedQuery<X> typedQuery = sessionFactory.openSession().createQuery(jcq);
       List<X> list = typedQuery.getResultList();
       return typedQuery;
   }

   public <X> TypedQuery<X> createCriteria(X entity, CriteriaQueryCallback<X> callback) {
       Class<X> clazz = (Class<X>) entity.getClass();
       // HibernateCriteriaBuilder hcb = sessionFactory.getCriteriaBuilder();
       HibernateCriteriaBuilder hcb = getSession().getCriteriaBuilder();
       JpaCriteriaQuery<X> jcq = hcb.createQuery(clazz);

       Map<String, Object> params = BeanUtils.toMap(entity);

       // 定义from子句
       Root<X> root = jcq.from(clazz);

       JpaPredicate jpaPredicate = callback.map(hcb, root, params);
       jcq.where(jpaPredicate);

       TypedQuery<X> typedQuery = entityManager.createQuery(jcq);
       // TypedQuery<X> typedQuery = sessionFactory.openSession().createQuery(jcq);
       List<X> list = typedQuery.getResultList();
       return typedQuery;
   }

    // @Override
    // public int delete(Employee employee) {
    //     employeeDao.delete(employee);
    //     return 1;
    // }

    @Override
    public Employee get(String id) {
        Optional<Employee> optional = employeeDao.findById(id);
        Employee employee = new Employee();
        employee.setId(id);
        return optional.orElse(employee);
    }

    public List<Employee> findEmployeeByExample(String firstName, String lastName) {
        Employee probe = new Employee();
        probe.setName(firstName);

        ExampleMatcher matcher = ExampleMatcher.matching()
                .withIgnoreCase() // 忽略大小写匹配
                .withStringMatcher(ExampleMatcher.StringMatcher.CONTAINING); // 使用模糊匹配

        Example<Employee> example = Example.of(probe, matcher);

        return employeeDao.findAll(example);
    }
}
